package cn.zou.db;

import cn.zou.utils.CastSqlOrBean;
import org.apache.log4j.Logger;
import org.junit.Test;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.*;


/**
 * Created by zou on 2017/7/11.
 */
public class DBServer<T> {
    //javabean的Class类型
    Logger logger=Logger.getLogger(DBServer.class);
    private HashMap<String,String> mappingrelation=null;
    //字段加载类
    private  TableLoad tableLoad=new TableLoad();
    Class<T> aClass;
    //DB操作
    private DBOperation dp=null;
    {
        dp=new DBOperation();
    }

    /**
     *
     * @param sql 数据库插入语句
     * @return
     */
    public int insert(String sql){
        return dp.executeUpdate(sql);
    }

    /**
     * 参数化查询对象
     * @param sql 带参数的SQL语句
     * @param map 参数
     * @return
     */
    public int insert(String sql, HashMap<Integer,Object> map){
        return dp.executeUpdate(sql,map);
    }

    public int inser(T t){
        //获取get方法的值的集合
        try {
            StringBuffer strSQL=new StringBuffer();
            aClass = (Class<T>) t.getClass();//获取到期当前的class对象
            Field[] declaredFields = aClass.getDeclaredFields();
            String[] filedNames = filedName(declaredFields);
             Map<String, Object> cloumMap = tableLoad.loadCloum(filedNames, aClass);
            //连接表名
            String tablename=cloumMap.get("tableName").toString();
            strSQL.append("INSERT INTO ");
            strSQL.append(tablename+" ");
            strSQL.append("VALUES(");
            for(int i=0;i<filedNames.length;i++){
                //判断该字段是否为数据库有对应 也就是在配置文件中是否写入
                if(cloumMap.get(filedNames[i])!=null){
                    Method method = getMethod(aClass,filedNames[i]);
                    Object o = method.invoke(t,null);
                    String returnValue=o.toString();
                    if(o instanceof Date){
                        SimpleDateFormat  simpleDateFormat=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                        returnValue=simpleDateFormat.format((Date)o);
                    }
                    if(returnValue==null){
                        strSQL.append("NULL");
                        if(i!=(filedNames.length-1)){
                            strSQL.append(",");
                        }
                    }else{
                        strSQL.append("'"+returnValue+"'");
                        if(i!=(filedNames.length-1)){
                            strSQL.append(",");
                        }
                    }
                }
            }
            strSQL.append(")");
            return insert(strSQL.toString());
        } catch (Exception e) {
            e.printStackTrace();
            logger.error(e);
        }
        //插入不需要获取字段

        return 0;
    }
    public int  update(String sql){return dp.executeUpdate(sql);}
    public int  update(String sql,HashMap<Integer,Object> parms){return dp.executeUpdate(sql,parms);}
    /**
     * 以ORM思想操作数据库
     * @param t
     * @return
     */
    public int  update(T t){
        try {
            //参数计数器
            int count=1;
            StringBuffer strSQL=new StringBuffer();
            aClass = (Class<T>) t.getClass();//获取到期当前的class对象
            //获取到所以字段
            String[] filedNames = filedName(aClass.getDeclaredFields());
            //获取到字段对应
            Map<String, Object> cloum = tableLoad.loadCloum(filedNames, aClass);
            //获取表名
            String tablename=cloum.get("tableName").toString();
            strSQL.append("UPDATE ");
            strSQL.append(tablename+" SET ");
            HashMap<Integer,Object> parms=new HashMap<Integer,Object>();
            //列名 价值
            for(int i=0;i<filedNames.length;i++){
                    if(cloum.get(filedNames[i])!=null&&!isPk(cloum,filedNames[i])){
                        Method method1=getMethod(aClass,filedNames[i]);
                        Object o = method1.invoke(t, null);
                        //判断其是否有返回值
                        if(o!=null){
                            strSQL.append(" "+cloum.get(filedNames[i]).toString()+"= ? ");
                            parms.put(count++,o);
                            if(i!=(filedNames.length-1)){
                                strSQL.append(",");
                            }
                        }
                    }
            }
            //开始where 语句
            strSQL.append(" WHERE 1=1");
            List<String> pk = (List)cloum.get("pk");
            for(String pkName:pk){
                Method method =getMethod(aClass, pkName);
                Object o = method.invoke(t, null);
                if(o==null)throw new NullPointerException("主键不能为空");
                strSQL.append(" and "+pkName+"= ? ");
                parms.put(count++,o.toString());
            }
            return update(strSQL.toString(),parms);
        } catch (Exception e) {
            e.printStackTrace();
            return -1;
        }
    }
    public int delete(String sql){return dp.executeUpdate(sql);}
    public int delete(String sql,Map<Integer,Object> prams){return dp.executeUpdate(sql,prams);}
    /**
     * 以ORM的思想来进行删除
     */
    public int delete(T t) throws SQLException {
        try {
            StringBuffer sql=new StringBuffer();//数据库语句
            //获取到期claa对象
            int count=1;
            Map<Integer,Object> prams=new HashMap<Integer, Object>();
            if(aClass==null) aClass=(Class<T>)t.getClass();
            String[] filedName = filedName(aClass.getDeclaredFields());
            Map<String, Object> cloums = tableLoad.loadCloum(filedName,aClass);
            sql.append("DELETE ");
            sql.append("FROM "+cloums.get("tableName")+" ");
            sql.append("WHERE 1=1 ");
            if(cloums.get("pk")==null)
                throw new SQLException("没有配置对应主键");
            List<String > pks=(List<String>) cloums.get("pk");
            for(String pk:pks){
                Method method = getMethod(aClass, pk);
                Object invoke = method.invoke(t, null);
                sql.append("AND ");
                sql.append(pk+"= ? ");
                prams.put(count++,invoke);
            }
            return dp.executeUpdate(sql.toString(),prams);
        } catch (Exception e) {
            throw new SQLException(e);
        }

    }
    /**
     * 根据自己定义的sql进行查询
     */
    public ResultSet select(String sql){return dp.executeQuery(sql);}
    /**
     * 有参数化的查询
     */
    public ResultSet select(String sql,Map<Integer,Object> parms){return dp.executeQuery(sql,parms);}
    /**
     * 根据主键获取
     * @return
     */
    public ResultSet getBean(T t) throws SQLException {
        try {
            StringBuffer sql=new StringBuffer();//数据库语句
            //获取到期claa对象
            int count=1;
            Map<Integer,Object> prams=new HashMap<Integer, Object>();
            if(aClass==null) aClass=(Class<T>)t.getClass();
            String[] filedName = filedName(aClass.getDeclaredFields());
            Map<String, Object> cloums = tableLoad.loadCloum(filedName,aClass);
            sql.append("SELECT *");
            sql.append("FROM "+cloums.get("tableName")+" ");
            sql.append("WHERE 1=1 ");
            if(cloums.get("pk")==null)
                throw new SQLException("没有配置对应主键");
            List<String > pks=(List<String>) cloums.get("pk");
            for(String pk:pks){
                Method method = getMethod(aClass, pk);
                Object invoke = method.invoke(t, null);
                sql.append("AND ");
                sql.append(pk+"= ? ");
                prams.put(count++,invoke);
            }
            return dp.executeQuery(sql.toString(),prams);
        } catch (Exception e) {
            throw new SQLException(e);
        }
    }
    /**
     *
     */
    private Method getMethod(Class clzz,String filenname) throws NoSuchMethodException{
        //获取存在此字段
        String s=filenname.substring(0,1).toUpperCase();
        //获取方法名
        String method="get"+s+filenname.substring(1);
        return  aClass.getMethod(method);
    }
    /**
     * 判断该参数是否为主键
     * @param cloum
     * @return
     */
    private boolean  isPk(Map<String, Object> cloums,String cloum){
        List<String> pk = (List)cloums.get("pk");
        for(String pkname:pk){
            if(pkname.equals(cloum))return true;
        }
        return false;
    }
    public  String[] filedName(Field[] declaredFields){
        String[] filedNames=new String[declaredFields.length];
        for(int i=0;i<declaredFields.length;i++){
            Field declaredField = declaredFields[i];
            declaredField.setAccessible(true);
            filedNames[i]=declaredField.getName();
        }
        return filedNames;
    }
    @Test
    public void testInsert() throws SQLException, IllegalAccessException, InstantiationException {
        DBServer<Person> db=new DBServer<Person>();
        ResultSet bean = db.select("SELECT * FROM person");
        List<Object> beans = CastSqlOrBean.getBeans(bean, Person.class);
      for(Object p:beans){
          System.out.println(((Person)p).toString());
      }
    }
}
